package com.dang.alex.dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.dang.alex.dao.daoInterface.ProductDAO;
import com.dang.alex.model.Book;
import com.dang.alex.model.Product;
import com.dang.alex.util.JdbcUtil;

public class ProductDAOImp implements ProductDAO{

	private final static String FIND_BY_ID="select * from d_product p left join d_book b on b.id=p.id where p.id=?";
	private final static String FIND_BY_HOT="select * from d_item it left join d_product p on p.id=it.product_id left join d_book b on b.id=p.id group by it.product_id order by product_num desc limit ?;";
			
			
	private final static String FIND_BY_NEW="select * from d_product p left join d_book b on b.id=p.id order by p.add_time desc limit ?";
			
	
	private final static String FIND_BY_NEW_HOT="select sum(product_num) as num,p.*,b.* from d_item i join d_product p on i.product_id=p.id join d_book b on i.product_id=b.id where p.add_time>? group by product_id order by num desc limit 0,?";
			
	
	public Book findById(int id) throws Exception{
		Connection conn=null;
		PreparedStatement prep=null;
		ResultSet rs=null;
		Book book=null;
		
		try {
			prep=JdbcUtil.getConnection().prepareStatement(FIND_BY_ID);
			prep.setInt(1, id);
			rs=prep.executeQuery();
			if(rs.next()){
				book=new Book();
				book.setId(rs.getInt("id"));
				book.setAuthor(rs.getString("author"));
				book.setPublishing(rs.getString("publishing"));
				book.setPublishDt(rs.getLong("publish_time"));
				book.setWordNumber(rs.getString("word_number"));
				book.setWhichEdtion(rs.getString("which_edtion"));
				book.setTotalPage(rs.getString("total_page"));
				book.setPrintDt(rs.getLong("print_time"));
				book.setPrintNumber(rs.getString("print_number"));
				book.setIsbn(rs.getString("isbn"));
				book.setAuthorSummary(rs.getString("author_summary"));
				book.setCatalogue(rs.getString("catalogue"));
				
				book.setProductName(rs.getString("product_name"));
				book.setDescription(rs.getString("description"));
				book.setAddTime(rs.getLong("add_time"));
				book.setFixedPrice(rs.getDouble("fixed_price"));
				book.setDangPrice(rs.getDouble("dang_price"));
				book.setKeywords(rs.getString("keywords"));
				book.setHasDeleted(rs.getInt("has_deleted"));
				book.setProductPic(rs.getString("product_pic"));
				
				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.close(prep, conn, rs);
		}
		
		
		return book;
	}

	@Override
	public List<Product> findByHot(int i) throws Exception {
		Connection conn=null;
		PreparedStatement prep=null;
		ResultSet rs=null;
		List<Product> books=new ArrayList<Product>();
		
		try {
			prep=JdbcUtil.getConnection().prepareStatement(FIND_BY_HOT);
			prep.setInt(1, i);
			rs=prep.executeQuery();
			while(rs.next()){
				Book book=new Book();
				book.setId(rs.getInt("id"));
				book.setAuthor(rs.getString("author"));
				book.setPublishing(rs.getString("publishing"));
				book.setPublishDt(rs.getLong("publish_time"));
				book.setWordNumber(rs.getString("word_number"));
				book.setWhichEdtion(rs.getString("which_edtion"));
				book.setTotalPage(rs.getString("total_page"));
				book.setPrintDt(rs.getLong("print_time"));
				book.setPrintNumber(rs.getString("print_number"));
				book.setIsbn(rs.getString("isbn"));
				book.setAuthorSummary(rs.getString("author_summary"));
				book.setCatalogue(rs.getString("catalogue"));
				
				book.setProductName(rs.getString("product_name"));
				book.setDescription(rs.getString("description"));
				book.setAddTime(rs.getLong("add_time"));
				book.setFixedPrice(rs.getDouble("fixed_price"));
				book.setDangPrice(rs.getDouble("dang_price"));
				book.setKeywords(rs.getString("keywords"));
				book.setHasDeleted(rs.getInt("has_deleted"));
				book.setProductPic(rs.getString("product_pic"));
				
				
				books.add(book);
				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.close(prep, conn, rs);
		}
		
		
		return books;
	}

	

	public List<Product> findByNew(int i) throws Exception{
		Connection conn=null;
		PreparedStatement prep=null;
		ResultSet rs=null;
		List<Product> books=new ArrayList<Product>();
		
		try {
			prep=JdbcUtil.getConnection().prepareStatement(FIND_BY_NEW);
			prep.setInt(1,i);
			rs=prep.executeQuery();
			while(rs.next()){
				Book book=new Book();
				book.setId(rs.getInt("id"));
				book.setAuthor(rs.getString("author"));
				book.setPublishing(rs.getString("publishing"));
				book.setPublishDt(rs.getLong("publish_time"));
				book.setWordNumber(rs.getString("word_number"));
				book.setWhichEdtion(rs.getString("which_edtion"));
				book.setTotalPage(rs.getString("total_page"));
				book.setPrintDt(rs.getLong("print_time"));
				book.setPrintNumber(rs.getString("print_number"));
				book.setIsbn(rs.getString("isbn"));
				book.setAuthorSummary(rs.getString("author_summary"));
				book.setCatalogue(rs.getString("catalogue"));
				
				book.setProductName(rs.getString("product_name"));
				book.setDescription(rs.getString("description"));
				book.setAddTime(rs.getLong("add_time"));
				book.setFixedPrice(rs.getDouble("fixed_price"));
				book.setDangPrice(rs.getDouble("dang_price"));
				book.setKeywords(rs.getString("keywords"));
				book.setHasDeleted(rs.getInt("has_deleted"));
				book.setProductPic(rs.getString("product_pic"));
				
				books.add(book);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.close(prep, conn, rs);
		}
		
		
		return books;
	}

	

}
